package cn.com.klun.tjy.other.repairDate;

import cn.com.klun.tjy.other.util.DataConnectUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class RepairStockDayInfo {

    /**
     * 使用java原生的jdbc链接数据库
     * @param args
     */
    public static void main(String[] args) {
        String diver  = "com.mysql.jdbc.Driver";
        String url  = "jdbc:mysql://39.107.115.53:3306/tjy";
        String account  = "root";
        String pword  = "root";
        Connection conn = DataConnectUtil.getConn(diver, url, account, pword);
        //查询sql
        String sql = "select tt.STO_ID ID from(" +
                "select count(1) scount,a.STO_ID,a.TRN_DATE from TBL_STOCK_DAY_INFOS a LEFT JOIN (" +
                "select TRN_DATE from TBL_STOCK_DAY_INFOS GROUP BY  TRN_DATE)b " +
                "on a.TRN_DATE = b.TRN_DATE GROUP BY a.STO_ID HAVING scount > 25) tt ";

        String sql1 = "select count(1) nn,TRN_DATE, STO_ID,ID from TBL_STOCK_DAY_INFOS where STO_ID = ? GROUP BY TRN_DATE HAVING nn > 10";

        String sql2 = "delete from TBL_STOCK_DAY_INFOS where STO_ID = ?  and TRN_DATE = ? and ID not in (?)";
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            int count = 0;
            while(resultSet.next()){
                String id = resultSet.getString("ID");
                PreparedStatement ps1 = conn.prepareStatement(sql1);
                System.out.println("111111111111111"+id);
                ps1.setString(1,id);
                ResultSet resultSet1 = ps1.executeQuery();
                while (resultSet1.next()){
                    String TRN_DATE = resultSet1.getString("TRN_DATE");
                    String STO_ID = resultSet1.getString("STO_ID");
                    String ID = resultSet1.getString("ID");
                    System.out.println("2222222222222"+TRN_DATE+STO_ID+ID);
                    PreparedStatement ps2 = conn.prepareStatement(sql2);
                    ps2.setString(1,STO_ID);
                    ps2.setString(2,TRN_DATE);
                    ps2.setString(3,ID);
                    int i = ps2.executeUpdate();
                    System.out.println("3333333333333"+i);
                    if(i < 8){
                        conn.rollback();
                    }
                    conn.commit();
                    count++;
                }
            }
            conn.setAutoCommit(true);
            System.out.println("++++++++++++++:"+count);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
